package cong;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import cong.Note;
public class Dbconnection {
	//
	private Connection connection = null;
	//
	private String sql_driver = "com.mysql.jdbc.Driver";
	private String sql_url = "jdbc:mysql://localhost/NoteSys";
	private String sql_username = "root";
	private String sql_password = "000000";
	//建立数据库
	public Dbconnection(){
		try{
			Class.forName(sql_driver);
			connection = DriverManager.getConnection(sql_url, sql_username, sql_password);
			System.out.println("ok");
		}catch(Exception exc){
			exc.printStackTrace();
		}
	}
	//执行各种sql语句
	private ResultSet execSql(String sql, Object ...args)throws SQLException
	{
		PreparedStatement pstmt = connection.prepareStatement(sql);
		for(int i = 0; i < args.length; i++){
			pstmt.setObject(1 + i, args[i]);
		}
		pstmt.execute();
		return pstmt.getResultSet();
	}
	//插入留言
	public boolean addNote(Note note){
		try{
			String sql = "insert into note_table(title, content, contact) values(?, ?, ?)";
			execSql(sql, note.getTitle(), note.getContent(), note.getContact());
			return true;
		}catch(Exception exc){
			exc.printStackTrace();	
			return false;	
		}
		
	}
	//获取留言信息
	public ArrayList<Note> getNote(){
		ArrayList<Note> arrayList = new ArrayList<Note>();
		try{
			String sql = "select * from note_table";
			ResultSet resultSet = execSql(sql);
			while(resultSet.next()){
				Note note = new Note();
				note.setTitle(resultSet.getString("title"));
				note.setContent(resultSet.getString("content"));
				note.setContact(resultSet.getString("contact"));
				arrayList.add(note);				
			}
			return arrayList;
		}catch(Exception exc){
			exc.printStackTrace();
			return null;
		}
	}
}
